# -*- coding: utf-8 -*-
#各种查询，决策分析，报表
class StockController < ApplicationController
  before_filter :employee_authorize
  before_filter :sys_before_log
  before_filter :employee_action_hx
  before_filter :privilege_filter, :except => [:statisticsSaleDeliveryView, :statisticsPurchaseEntryView, :statisticsLoanDeliveryView, :statisticsSaleReturnedEntryView, :simpleStockForToolTipByProductId, :statisticsLoanDelivery]

  #库管分析决策
  def analysis

  end

  #库存查询主页
  def index

  end

  #综合库存查询
  def productQuery
    start = params[:start]
    limit = params[:limit]
    begin
      queryStr = "pc.is_locked != 1"
      subQueryStr = "is_locked != 1"
      queryHash = Hash.new
      productId = params[:product_id]
      productTypeId = params[:product_type_id]
      warehouseId = params[:warehouse_id]
      #是否显示详细批次，即是否显示库存中的货，是通过什么途径来的。
      batchInfo = params[:batch_info]
      beginDate = params[:begin_date]
      endDate = params[:end_date]
      orderType = params[:order_type]
      companyId = params[:company_id]
      brandId = params[:brand_id]

      #只有在类别这空的情况下，才考虑产品ID
      if productTypeId == "" || productTypeId == nil
        if productId != "" && productId != "null" && productId != nil
          queryStr = "pc.product_id = :product_id"
          subQueryStr = "product_id = #{productId}"
          queryHash[:product_id] = productId
        end
      else
        queryStr = "p.product_type_id in (:product_type_id)"
        #subQueryStr = "p.product_type_id in (#{productTypeId})"
        queryHash[:product_type_id] = productTypeId
      end

      if endDate != "" && endDate != nil
        queryStr = queryStr + " AND pc.action_date <= :action_date"
        subQueryStr = subQueryStr + " AND action_date <= '#{endDate}'"
        queryHash[:action_date] = endDate
      end

      if orderType != "" && orderType != nil && orderType != "null"
        queryStr = queryStr + " AND pc.resource_type = '#{orderType}'"
        subQueryStr = subQueryStr + " AND resource_type = '#{orderType}'"
      end

      if companyId != "" && companyId != nil && companyId != "null"
        queryStr = queryStr + " AND pc.company_id = '#{companyId}'"
        subQueryStr = subQueryStr + " AND company_id = '#{companyId}'"
      end

      if brandId != "" && brandId != nil && brandId != "null"
        queryStr = queryStr + " AND p.brand_id = '#{brandId}'"
        subQueryStr = subQueryStr + " AND p.brand_id = '#{brandId}'"
      end

      #把已经撤销的爬出在外。
      queryStr = queryStr + " AND pc.is_locked = 0"
      subQueryStr = subQueryStr + " AND is_locked = 0"
      #根据角色,得到用户可查询的仓库范围

      #undo 未完成2012-11-30，在选择仓库时，要判断角色的仓库权限。如果用户选择了某一个仓库，要判断是否对这个仓库具有访问权。
      warehouseIds = RoleWarehouse.getRoleWarehouseUnlockedArray(session[:company_id], session[:role_id])
      if warehouseIds.size() > 0
        if warehouseId != nil && warehouseId != "" && warehouseId != "null"
          if  warehouseIds.include?(warehouseId.to_i)
            queryStr = queryStr + " AND pc.warehouse_id = :warehouse_id"
            subQueryStr = subQueryStr + " AND warehouse_id = #{warehouseId}"
            queryHash[:warehouse_id] = warehouseId
          end
        else
          queryStr = queryStr + " AND pc.warehouse_id in (:warehouse_id)"
          queryHash[:warehouse_id] = warehouseIds
          warehouseId = warehouseIds
          subQueryStr = subQueryStr + " AND warehouse_id in (#{warehouseIds.join(",")})"
        end
      end

      #这儿是判断是否显示详细批次
      if batchInfo == "true"
        groupCondition = "pc.product_id,pc.warehouse_id,pc.resource_id,pc.resource_type"
        subQueryStr = subQueryStr + " AND auditing_status = '' AND product_id = pc.product_id AND warehouse_id = pc.warehouse_id AND resource_id = pc.resource_id AND resource_type = pc.resource_type"
        selectStr = "pc.id,product_id,warehouse_id,0 as begin_quantity,sum(quantity) as projected_quantity,(select sum(quantity) as quantity from product_collections where #{subQueryStr}) as quantity,resource_id,resource_type,pc.description,action_date,pc.created_at"
      else
        groupCondition = "pc.product_id,pc.warehouse_id"
        subQueryStr = subQueryStr + " AND auditing_status = '' AND product_id = pc.product_id AND warehouse_id = pc.warehouse_id"
        selectStr = "pc.resource_id,pc.resource_type,pc.id,product_id,warehouse_id,0 as begin_quantity,sum(quantity) as projected_quantity,(select sum(quantity) as quantity from product_collections where #{subQueryStr}) as quantity,pc.description,action_date,pc.created_at"
      end


      if batchInfo == "true"
        productCollectionsCount = ProductCollection.joins(' pc left join products p on pc.product_id = p.id').where([queryStr, queryHash]).select("pc.id").group(groupCondition)
        if params[:sub_type].nil?
          productCollections = ProductCollection.includes([:warehouse, :product, :resource]).where([queryStr, queryHash]).select("#{selectStr}").joins(' pc left join products p on pc.product_id = p.id').offset(start.to_i).limit(limit.to_i).group(groupCondition)
        else
          productCollections = ProductCollection.includes([:warehouse, :product, :resource]).where([queryStr, queryHash]).select("#{selectStr}").joins(' pc left join products p on pc.product_id = p.id').group(groupCondition)
        end
      else
        productCollectionsCount = ProductCollection.joins(' pc left join products p on pc.product_id = p.id').where([queryStr, queryHash]).select("pc.id").group(groupCondition)
        if params[:sub_type].nil?
          productCollections = ProductCollection.includes([:warehouse, :product]).where([queryStr, queryHash]).select("#{selectStr}").joins(' pc left join products p on pc.product_id = p.id').group(groupCondition).offset(start.to_i).limit(limit.to_i)
        else
          productCollections = ProductCollection.includes([:warehouse, :product]).joins(' pc left join products p on pc.product_id = p.id').where([queryStr, queryHash]).select("#{selectStr}").group(groupCondition)
        end
      end
      #计算库存产品的总价值。
      queryStr = queryStr + " AND pc.product_id = p.id"
      ##costPCs = ProductCollection.where([queryStr, queryHash]).select("sum(pc.quantity * p.cost_price) as sum_cost_amount,sum(pc.quantity * p.tax_price) as sum_tax_amount").from('product_collections pc,products p').group("pc.product_id").first
      if warehouseId.class.name == "Array"
        warehouseId = warehouseId.join(',')
      end
      costPCs = ProductCollection.find_by_sql("SELECT SUM(pc.quantity * #{session[:financial_management] == "是" ? "p.cost_price" : "0"}) as sum_cost_amount,SUM(pc.quantity * #{session[:financial_management] == "是" ? "p.tax_price" : "0"}) as sum_tax_amount FROM `product_collections` pc left join products p on pc.product_id = p.id WHERE #{queryStr.sub(/:warehouse_id/, warehouseId).sub(/pc.product_id = :product_id AND/, '').sub(/pc.action_date <= :action_date AND/, '').sub(/p.product_type_id in \(:product_type_id\) AND/, '')}")

      sumCostAmount = 0
      sumTaxAmount = 0
      if costPCs != nil && session[:financial_management] == "是"
        sumCostAmount = costPCs[0].sum_cost_amount
        sumTaxAmount = costPCs[0].sum_tax_amount
      end

      #测试数据
      #由于在测试过程中，这个表中会产生垃圾数据，比如出库的数据已经删了，但盘点表中，依然没有删。
      #下面这块代码先屏蔽了，因为现在的记录都是不删的。
      #      faultProductArray = Array.new
      #      productCollections.each do |record|
      #        if record == nil || record.resource == nil
      #          faultProductArray << record
      #        end
      #      end
      #
      #      faultProductArray.each do |record|
      #        productCollections.delete(record)
      #      end
      #jsonStr="{totalProperty:"+productCollectionsCount.length.to_s+",root:"+productCollections.to_json(:include=>[:product,:warehouse,:resource])+"}"
      #判断是输出报表，还是输出Json

      #如果是不详细求得批次信息的,同时又有开始时间的,则再次查询一次时间段,求得开始时间时,库存情,且只求当时的准确库存情况.
      productIds = []

      if beginDate != "" && beginDate != nil && batchInfo != "true"
        productCollections.each do |endRecord|
          productIds << endRecord.product_id
        end
        productIds = productIds.uniq()
        #以不重复的产品数组为条件,查询出开始日期时的产品库存.
        #beginRecords = ProductCollection.where("product_id in (?) AND action_date <= ?",productIds,beginDate).select("sum(quantity)").group("product_id,warehouse_id")

        beginRecords = ProductCollection.where("product_id in (?) AND action_date <= ?", productIds, beginDate).select("sum(quantity) as quantity,product_id,warehouse_id").group("product_id,warehouse_id")
        #以下each操作，是为求得起始库存的数量
        productCollections.each do |endRecord|
          beginRecords.each do |beginRecord|
            if (endRecord.product_id == beginRecord.product_id && endRecord.warehouse_id == beginRecord.warehouse_id)
              endRecord.begin_quantity = beginRecord.quantity
              break
            end
          end
        end
      end

      if params[:sub_type].nil?
        jsonStr="{totalProperty:"+productCollectionsCount.length.to_s+",sum_cost_amount:#{sumCostAmount},sum_tax_amount:#{sumTaxAmount}"+",root:"+productCollections.to_json(:include => {:product => {:only => [:id, :name, :number, :cost_price]}, :warehouse => {:only => [:id, :name]}, :resource => {:only => [:id, :order_number]}})+"}"
        #logger.info jsonStr
        render :text => jsonStr, :layout => false
      else
        require 'spreadsheet'
        Spreadsheet.client_encoding = "UTF-8"
        bookString = StringIO.new
        book = Spreadsheet.open "#{Rails.root.to_s}/report_template/stock_query.xls"
        i = 4
        sheet1 = book.worksheet 0

        sheet1[3, 0] = "产品名称"
        sheet1[3, 1] = "产品编号"
        sheet1[3, 2] = "平均成本"
        sheet1[3, 3] = "起始数量"
        sheet1[3, 4] = "实际数量"
        sheet1[3, 5] = "预计数量"
        sheet1[3, 6] = "仓库名称"

        logger.info productCollections.length
        productCollections.each do |productCollection|
          sheet1[i, 0] = productCollection.product.name
          sheet1[i, 1] = productCollection.product.number
          sheet1[i, 2] = session[:financial_management] == "是" ? productCollection.product.cost_price : 0
          if beginDate != "" && beginDate != nil && batchInfo != "true" && session[:financial_management] == "是"
            sheet1[i, 3] = productCollection.begin_quantity
          end
          sheet1[i, 4] = productCollection.quantity
          sheet1[i, 5] = productCollection.projected_quantity.to_i
          sheet1[i, 6] = productCollection.warehouse.name
          i += 1
        end

        #sheet1[i,9] = sumCostPriceAmount
        #sheet1[i,11] = sumTaxPriceAmount

        book.write bookString
        send_data bookString.string, :filename => '库存报表.xls'
      end

    rescue Exception => e
      logger.info e
      DbLog.info(session[:company_id], session[:department_id], session[:employee_id], params[:controller], params[:action], request.remote_ip, e.to_s)
    end
  end

  #得到仓库中的产品数量
  def getProductStockQuantity
    quantity1 = ProductCollection.where("product_id = ? AND warehouse_id = 1", params[:id]).sum(:quantity)
    quantity2 = ProductCollection.where("product_id = ? AND warehouse_id = 2", params[:id]).sum(:quantity)
    render :text => quantity1.to_s + "|" + quantity2.to_s, :layout => false
  end


  #统计采购入库
  def statisticsPurchaseEntry
    # 首先计算一下成本价。
    #Product.cost_price
    #begin
    start = params[:start]
    limit = params[:limit]
    #以入库时间分析出年，月
    calYear = params[:begin_date].split(/-/)[0]
    calMonth = params[:begin_date].split(/-/)[1].to_i
    conditionsStr = ''

    if params[:purchase_order_number].to_s != ''
      conditionsStr = conditionsStr + "po.seq_number in (#{params[:purchase_order_number]})"
    end

    if params[:auditing_status].to_s != '' && params[:auditing_status] != ' '
      if conditionsStr == ''
        conditionsStr = 'auditing_status like :auditing_status'
      else
        conditionsStr = conditionsStr+' AND auditing_status like :auditing_status'
      end
      conditionsHash[:auditing_status] = '%'+params[:auditing_status].to_s+'%'
    elsif params[:auditing_status] == ' '
      if conditionsStr == ''
        conditionsStr = 'auditing_status = :auditing_status'
      else
        conditionsStr = conditionsStr+' AND auditing_status = :auditing_status'
      end
      conditionsHash[:auditing_status] = ''
    end

    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "wed.updated_at >= '#{params[:begin_date]}'"
      else
        conditionsStr = conditionsStr+" AND wed.updated_at >= '#{params[:begin_date]}'"
      end
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "wed.updated_at <= '#{params[:end_date]}'"
      else
        conditionsStr = conditionsStr+" AND wed.updated_at <= '#{params[:end_date]}'"
      end
    end

    if params[:vendor_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "v.id = #{params[:vendor_id]}"
      else
        conditionsStr = conditionsStr+" AND v.id = #{params[:vendor_id]}"
      end
    end

    #显示产品
    if params[:product_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "wed.product_id = #{params[:product_id]}"
      else
        conditionsStr = conditionsStr+" AND wed.product_id = #{params[:product_id]}"
      end
    end


    if conditionsStr != ""
      conditionsStr = "where " + conditionsStr
    end

    if params[:entry_info] == "true"
      #要对where再处理一下。以不显示数量为0即没有进货的入库详细表信息
      #      if conditionsStr != ""
      #        conditionsStr = conditionsStr + "AND ed.quantity is not null"
      #      else
      #        conditionsStr = "where ed.quantity is not null"
      #      end
      purchaseOrders = PurchaseOrder.all(:select => "po.id as purchase_order_id,po.auditing_status,po.order_number as purchase_order_number,ed.order_number as entry_order_number,po.action_date ,po.auditing_status ,pd.quantity as purchase_quantity,p.name as product_name,ifnull(ed.quantity,0) as entry_quantity ,d.name as department_name,po.store_status,v.name as vendor_name", :joins => "po
left join purchase_order_details pd on po.id = pd.purchase_order_id AND pd.is_locked = 0
left join (select we.order_number,ed.purchase_order_id,ed.sale_order_id,ed.product_id,sum(ed.quantity) quantity from warehousing_entries we
           left join warehousing_entry_details ed on we.id = ed.warehousing_entry_id where we.auditing_status != '#{Constants::PASS_APPLY_CANCEL}' group by ed.id) ed
           on pd.sale_order_id = ed.sale_order_id AND pd.product_id = ed.product_id AND pd.purchase_order_id = ed.purchase_order_id
left join products p on p.id = pd.product_id
left join departments d on d.id = po.department_id
left join vendors v on v.id = po.vendor_id
#{conditionsStr}")
    else
      #      purchaseOrders = PurchaseOrder.all(:select=>"po.id as purchase_order_id,po.auditing_status,po.order_number as purchase_order_number,po.action_date ,po.auditing_status ,pd.quantity as purchase_quantity,p.name as product_name,ifnull(ed.quantity,0) as entry_quantity ,d.name as department_name,po.store_status,v.name as vendor_name",:joins =>"po
      #left join purchase_order_details pd on po.id = pd.purchase_order_id AND pd.is_locked = 0
      #left join (select ed.purchase_order_id,ed.sale_order_id,ed.product_id,sum(ed.quantity) quantity from warehousing_entries we
      #           left join warehousing_entry_details ed on we.id = ed.warehousing_entry_id where we.auditing_status != '#{Constants::PASS_APPLY_CANCEL}' group by ed.sale_order_id,ed.purchase_order_id,ed.product_id) ed
      #           on pd.sale_order_id = ed.sale_order_id AND pd.product_id = ed.product_id AND pd.purchase_order_id = ed.purchase_order_id
      #left join products p on p.id = pd.product_id
      #left join departments d on d.id = po.department_id
      #left join vendors v on v.id = po.vendor_id
      #{conditionsStr} group by pd.id,p.id")
      warehousingEntries = WarehousingEntry.all(:select => "wed.id,we.order_number,we.auditing_status, wed.quantity ,we.updated_at,po.order_number as purchase_order_number,po.auditing_status as purchase_auditing_status,v.name as vendor_name,p.name as product_name,p.number as product_number,cp.cost_price as cost_price,cp.tax_price as tax_price,(wed.quantity * cp.cost_price) as cost_price_amount,(wed.quantity * cp.tax_price) as tax_price_amount", :joins => "we
        left join warehousing_entry_details wed on we.id = wed.warehousing_entry_id
        left join purchase_orders po on po.id = we.purchase_order_id
        left join products p on p.id = wed.product_id
        left join departments d on d.id = po.department_id
        left join vendors v on v.id = po.vendor_id
        left join cost_prices cp on cp.product_id = p.id AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth}
      #{conditionsStr} group by wed.id,wed.product_id")
    end
    sumCostPriceAmount = 0.00
    sumTaxPriceAmount = 0.00
    if warehousingEntries.length > 0
      warehousingEntries.each do |warehousingEntry|
        sumCostPriceAmount = sumCostPriceAmount + warehousingEntry.cost_price_amount.to_f
        sumTaxPriceAmount = sumTaxPriceAmount + warehousingEntry.tax_price_amount.to_f
      end
    end
    jsonStr="{totalProperty:"+warehousingEntries.length.to_s+",sum_cost_price_amount:#{sumCostPriceAmount},sum_tax_price_amount:#{sumTaxPriceAmount},root:"+warehousingEntries.to_json()+"}"
    #logger.info jsonStr
    render :text => jsonStr, :layout => false
  end


  #统计销售出库
  def statisticsSaleDelivery
    # 首先计算一下成本价。
    #Product.cost_price
    #begin
    start = params[:start]
    limit = params[:limit]
    #以入库时间分析出年，月
    calYear = params[:begin_date].split(/-/)[0]
    calMonth = params[:begin_date].split(/-/)[1].to_i
    conditionsStr = ''

    if params[:purchase_order_number].to_s != ''
      conditionsStr = conditionsStr + "so.seq_number in (#{params[:purchase_order_number]})"
    end

    if params[:auditing_status].to_s != '' && params[:auditing_status] != ' '
      if conditionsStr == ''
        conditionsStr = 'auditing_status like :auditing_status'
      else
        conditionsStr = conditionsStr+' AND auditing_status like :auditing_status'
      end
      conditionsHash[:auditing_status] = '%'+params[:auditing_status].to_s+'%'
    elsif params[:auditing_status] == ' '
      if conditionsStr == ''
        conditionsStr = 'auditing_status = :auditing_status'
      else
        conditionsStr = conditionsStr+' AND auditing_status = :auditing_status'
      end
      conditionsHash[:auditing_status] = ''
    end

    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "do.action_date >= '#{params[:begin_date]}'"
      else
        conditionsStr = conditionsStr+" AND do.action_date >= '#{params[:begin_date]}'"
      end
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "do.action_date <= '#{params[:end_date]}'"
      else
        conditionsStr = conditionsStr+" AND do.action_date <= '#{params[:end_date]}'"
      end
    end

    if params[:customer_id].to_s != '' && params[:customer_id].to_s != "null"
      if conditionsStr == ''
        conditionsStr = "c.id = #{params[:customer_id]}"
      else
        conditionsStr = conditionsStr+" AND c.id = #{params[:customer_id]}"
      end
    end

    #显示产品
    if params[:product_id].to_s != '' && params[:product_id].to_s != 'null'
      if conditionsStr == ''
        conditionsStr = "dod.product_id = #{params[:product_id]}"
      else
        conditionsStr = conditionsStr+" AND dod.product_id = #{params[:product_id]}"
      end
    end

    #把出库的，已经撤销的，去掉。
    if conditionsStr == ''
      conditionsStr = "do.auditing_status != '#{Constants::PASS_APPLY_CANCEL}'"
    else
      conditionsStr = conditionsStr+" AND do.auditing_status != '#{Constants::PASS_APPLY_CANCEL}'"
    end

    if conditionsStr != ""
      conditionsStr = "where " + conditionsStr
    end

    if params[:entry_info] == "true"
      #要对where再处理一下。以不显示数量为0即没有进货的入库详细表信息
      #      if conditionsStr != ""
      #        conditionsStr = conditionsStr + "AND ed.quantity is not null"
      #      else
      #        conditionsStr = "where ed.quantity is not null"
      #      end
    else
      saleDeliveries = DeliveryOrder.all(:select => "ss.name sales_strategy,#{session[:financial_management] == "是" ? "cp.cost_price as cost_price," : ""}
      #{session[:financial_management] == "是" ? "cp.tax_price as tax_price," : ""}
      #{session[:financial_management] == "是" ? "(dod.quantity * cp.cost_price) as cost_price_amount," : ""}
      #{session[:financial_management] == "是" ? "(dod.quantity * cp.tax_price) tax_price_amount," : ""}
      #{session[:sale_price_info] == "是" ? "sod.unit_price*sod.discount as sale_unit_price," : ""}
      #{session[:sale_price_info] == "是" ? "sod.unit_price*sod.discount*sod.quantity as sale_amount," : ""}
      st.name sale_type,dod.id,do.order_number,do.auditing_status,dod.quantity ,do.updated_at,so.order_number as sale_order_number,
      so.auditing_status as sale_auditing_status,c.name as customer_name,p.name as product_name,p.number as product_number",
                                         :joins => "do
        left join delivery_order_details dod on do.id = dod.delivery_order_id
        left join sale_orders so on so.id = do.sale_order_id
        left join sale_order_details sod on so.id = sod.sale_order_id AND sod.product_id = dod.product_id
        left join sale_types st on st.id = so.sale_type_id
        left join sales_strategies ss on ss.id = so.sales_strategy_id
        left join products p on p.id = dod.product_id
        left join departments d on d.id = so.department_id
        left join customers c on c.id = so.customer_id
        left join cost_prices cp on cp.product_id = p.id AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth}
                                         #{conditionsStr} group by dod.id,dod.product_id")
    end

    sumCostPriceAmount = 0.00
    sumTaxPriceAmount = 0.00
    if saleDeliveries.length > 0
      saleDeliveries.each do |deliveryOrder|
        sumCostPriceAmount = session[:financial_management] == "是" ? sumCostPriceAmount + deliveryOrder.cost_price_amount.to_f : 0
        sumTaxPriceAmount = session[:financial_management] == "是" ? sumTaxPriceAmount + deliveryOrder.tax_price_amount.to_f : 0
      end
    end
    #判断是输出报表，还是输出Json
    if params[:sub_type].nil?
      jsonStr="{totalProperty:"+saleDeliveries.length.to_s+",sum_cost_price_amount:#{sumCostPriceAmount},sum_tax_price_amount:#{sumTaxPriceAmount},root:"+saleDeliveries.to_json()+"}"
      #logger.info jsonStr
      render :text => jsonStr, :layout => false
    else
      require 'spreadsheet'
      Spreadsheet.client_encoding = "UTF-8"
      bookString = StringIO.new
      book = Spreadsheet.open "#{Rails.root.to_s}/report_template/sales_cost_report.xls"
      i = 4
      sheet1 = book.worksheet 0
      sheet1[3, 0] = "销售订单"
      sheet1[3, 1] = "审批状态"
      sheet1[3, 2] = "出库单"
      #sheet1[3,3] = "审批状态"
      sheet1[3, 4] = "客户"
      sheet1[3, 5] = "产品"
      sheet1[3, 6] = "编号"
      sheet1[3, 7] = "数量"
      sheet1[3, 8] = "销售单价"
      sheet1[3, 9] = "销售总额"
      sheet1[3, 10] = "销售类型"
      sheet1[3, 11] = "销售策略"
      sheet1[3, 12] = "出库日期"

      format = Spreadsheet::Format.new :color => :blue,
                                       :weight => :bold,
                                       :size => 18

      sheet1.default_format = format
      saleDeliveries.each do |saleDelivery|
        sheet1[i, 0] = saleDelivery.sale_order_number
        sheet1[i, 1] = saleDelivery.sale_auditing_status
        sheet1[i, 2] = saleDelivery.order_number
        #sheet1[i,3] = saleDelivery.delivery_auditing_status
        sheet1[i, 4] = saleDelivery.customer_name
        sheet1[i, 5] = saleDelivery.product_name
        sheet1[i, 6] = saleDelivery.product_number
        sheet1[i, 7] = saleDelivery.quantity
        if session[:sale_price_info] == "是"
          sheet1[i, 8] = saleDelivery.sale_unit_price
          sheet1[i, 9] = saleDelivery.sale_amount
        end
        sheet1[i, 10] = saleDelivery.sale_type
        sheet1[i, 11] = saleDelivery.sales_strategy
        sheet1[i, 12] = saleDelivery.updated_at.strftime("%Y-%m-%d").to_s
        i += 1
      end

      #sheet1[i,9] = sumCostPriceAmount
      sheet1[i, 9] = sumTaxPriceAmount

      book.write bookString
      send_data bookString.string, :filename => '销售出库分析.xls'
    end
  end

  #统计借货出库
  def statisticsLoanDelivery
    # 首先计算一下成本价。
    #Product.cost_price
    #begin
    start = params[:start]
    limit = params[:limit]
    #以入库时间分析出年，月
    calYear = params[:begin_date].split(/-/)[0]
    calMonth = params[:begin_date].split(/-/)[1].to_i
    conditionsStr = ''

    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "ld.action_date >= '#{params[:begin_date]}'"
      else
        conditionsStr = conditionsStr+" AND ld.action_date >= '#{params[:begin_date]}'"
      end
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "ld.action_date <= '#{params[:end_date]}'"
      else
        conditionsStr = conditionsStr+" AND ld.action_date <= '#{params[:end_date]}'"
      end
    end

    if params[:customer_id].to_s != '' && params[:customer_id].to_s != "null"
      if conditionsStr == ''
        conditionsStr = "c.id = #{params[:customer_id]}"
      else
        conditionsStr = conditionsStr+" AND c.id = #{params[:customer_id]}"
      end
    end

    #显示产品
    if params[:product_id].to_s != '' && params[:product_id].to_s != 'null'
      if conditionsStr == ''
        conditionsStr = "ldd.product_id = #{params[:product_id]}"
      else
        conditionsStr = conditionsStr+" AND ldd.product_id = #{params[:product_id]}"
      end
    end

    #把出库的，已经撤销的，去掉。
    if conditionsStr == ''
      conditionsStr = "ld.auditing_status != '#{Constants::PASS_APPLY_CANCEL}'"
    else
      conditionsStr = conditionsStr+" AND ld.auditing_status != '#{Constants::PASS_APPLY_CANCEL}'"
    end

    if conditionsStr != ""
      conditionsStr = "where " + conditionsStr
    end

    if params[:entry_info] == "true"
      #要对where再处理一下。以不显示数量为0即没有进货的入库详细表信息
      #      if conditionsStr != ""
      #        conditionsStr = conditionsStr + "AND ed.quantity is not null"
      #      else
      #        conditionsStr = "where ed.quantity is not null"
      #      end
    else
      loanDeliveries = LoanDelivery.joins("ld
        left join loan_delivery_details ldd on ld.id = ldd.loan_delivery_id
        left join loans lo on lo.id = ld.loan_id
        left join loan_details lod on lo.id = lod.loan_id AND lod.product_id = ldd.product_id
        left join products p on p.id = ldd.product_id
        left join departments d on d.id = lo.department_id
        left join customers c on c.id = lo.customer_id
        left join cost_prices cp on cp.product_id = p.id AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth} #{conditionsStr}").group(" ldd.id,ldd.product_id").select("ldd.id,ld.order_number,ld.auditing_status, ldd.quantity ,ld.updated_at,lo.order_number as loan_order_number,lo.auditing_status as loan_auditing_status,c.name as customer_name,p.name as product_name,p.number as product_number,cp.cost_price as cost_price,cp.tax_price as tax_price,(ldd.quantity * cp.cost_price) as cost_price_amount,(ldd.quantity * cp.cost_price) cost_price_amount,(ldd.quantity * cp.tax_price) tax_price_amount")
    end

    sumCostPriceAmount = 0.00
    sumTaxPriceAmount = 0.00
    if loanDeliveries.length > 0
      loanDeliveries.each do |deliveryOrder|
        sumCostPriceAmount = sumCostPriceAmount + deliveryOrder.cost_price_amount.to_f
        sumTaxPriceAmount = sumTaxPriceAmount + deliveryOrder.tax_price_amount.to_f
      end
    end
    #判断是输出报表，还是输出Json
    if params[:sub_type].nil?
      jsonStr="{totalProperty:"+loanDeliveries.length.to_s+",sum_cost_price_amount:#{sumCostPriceAmount},sum_tax_price_amount:#{sumTaxPriceAmount},root:"+loanDeliveries.to_json()+"}"
      #logger.info jsonStr
      render :text => jsonStr, :layout => false
    else
      require 'spreadsheet'
      Spreadsheet.client_encoding = "UTF-8"
      bookString = StringIO.new
      book = Spreadsheet.open "#{Rails.root.to_s}/report_template/loan_delivery_report.xls"
      i = 4
      sheet1 = book.worksheet 0
      sheet1[3, 0] = "借货订单"
      sheet1[3, 1] = "审批状态"
      sheet1[3, 2] = "出库单"
      #sheet1[3,3] = "审批状态"
      sheet1[3, 4] = "客户"
      sheet1[3, 5] = "产品"
      sheet1[3, 6] = "编号"
      sheet1[3, 7] = "数量"
      sheet1[3, 8] = "成本单价"
      sheet1[3, 9] = "成本总额"
      #sheet1[3,10] = "借货类型"
      #sheet1[3,11] = "借货策略"
      sheet1[3, 12] = "出库日期"

      format = Spreadsheet::Format.new :color => :blue,
                                       :weight => :bold,
                                       :size => 18

      sheet1.default_format = format
      loanDeliveries.each do |loanDelivery|
        sheet1[i, 0] = loanDelivery.loan_order_number
        sheet1[i, 1] = loanDelivery.loan_auditing_status
        sheet1[i, 2] = loanDelivery.order_number
        #sheet1[i,3] = loanDelivery.delivery_auditing_status
        sheet1[i, 4] = loanDelivery.customer_name
        sheet1[i, 5] = loanDelivery.product_name
        sheet1[i, 6] = loanDelivery.product_number
        sheet1[i, 7] = loanDelivery.quantity
        sheet1[i, 8] = loanDelivery.cost_price
        sheet1[i, 9] = loanDelivery.cost_price_amount
        #sheet1[i,10] = loanDelivery.sale_type
        #sheet1[i,11] = loanDelivery.sales_strategy
        sheet1[i, 12] = loanDelivery.updated_at.strftime("%Y-%m-%d").to_s
        i += 1
      end

      #sheet1[i,9] = sumCostPriceAmount
      sheet1[i, 9] = sumTaxPriceAmount

      book.write bookString
      send_data bookString.string, :filename => '借货出库分析.xls'
    end
  end

  #为采购，销售等提供简单的tooltip库存提示
  def simpleStockForToolTipByProductId
    productId = params[:product_id]
    @pcs = ProductCollection.joins("pc left join warehouses w on pc.warehouse_id = w.id").group("pc.product_id,pc.warehouse_id").where("pc.product_id=?", productId).select("w.name,sum(pc.quantity) quantity")
  end
end
